package com.ustcinfo.study.hive.r2.zhangweiwei;

import java.sql.*;

/**
 * 根据给定的数据，加载到数据库表中
 * 统计出相同域名的数据个数，打印输出
 *
 */
public class HiveConnection {

    // hive链接地址
    private static String url = "jdbc:hive2://node1:10000/default";

    //hive用户名
    private static String userName = "hive";

    // hive密码
    private static String passwd = "hive";

    // hive 驱动
    private static String driver = "org.apache.hive.jdbc.HiveDriver";

    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

    // 查询tables
    private static String showTableSql = "show tables";

    // 创建内部表dns_zhangweiwei  字段名
    private static String createSql = "create table dns_zhangweiwei(ip string,domain_name string,access_time string,target_ip string,status string)row format delimited fields terminated by '#' ";
    // 加载本地数据到dns_zhangweiwei
    private static String localDataSql = "load data local inpath '/root/hive-1.1.0-cdh5.7.0/dnsSample' into table dns_zhangweiwei ";

    // 统计出dns_zhangweiwei表中相同域名的数据个数
    private static String countSql = "select domain_name,count(domain_name) from dns_zhangweiwei group by domain_name";
    //删除同名表
    private static String countSql2="drop table dns_zhangweiwei";

    public static void main(String[] args) throws SQLException {
        //  加载hive驱动，并给出提示
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败");
            e.printStackTrace();
        }

        try {
            // 连接hive
            connection = DriverManager.getConnection(url, userName, passwd);
            if (connection != null) {
                System.out.println("hive 连接成功");
            } else {
                System.out.println("hive 连接失败");
            }

            statement = connection.createStatement();

            // 获取数据库中所有的表
            resultSet = statement.executeQuery(showTableSql);
            //如果有表名为dns_zhangweiwei的表
            // 先删除同名表
            while (resultSet.next()) {
                if(resultSet.getString(1).equals("dns_zhangweiwei")){
                    statement.execute(countSql2);
                    break;
                }
                System.out.println(resultSet.getString(1));
            }
            //创建表，加载数据
            statement.execute(createSql);
            statement.execute(localDataSql);

            //查询数据并输出结果
            resultSet = statement.executeQuery(countSql);
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1) + "\t" + resultSet.getInt(2));
            }


        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        } finally {
            if(connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        }

    }

}
